-- Hacer DELETE RESTRICT (era CASCADE) para la patas del comm (por triggers AFTER UPDATE y AFTER DELETE)

--artm
ALTER TABLE public.artm DROP CONSTRAINT gestion_ai_commartm;
ALTER TABLE public.artm
  ADD CONSTRAINT gestion_ai_commartm FOREIGN KEY (idcomm)
      REFERENCES public.comm (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;
--conm
ALTER TABLE public.conm DROP CONSTRAINT gestion_ai_commconm;
ALTER TABLE public.conm
  ADD CONSTRAINT gestion_ai_commconm FOREIGN KEY (idcomm)
      REFERENCES public.comm (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;
--ctbm
ALTER TABLE public.ctbm DROP CONSTRAINT gestion_ai_commctbm;
ALTER TABLE public.ctbm
  ADD CONSTRAINT gestion_ai_commctbm FOREIGN KEY (idcomm)
      REFERENCES public.comm (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;
--ctem
ALTER TABLE public.ctem DROP CONSTRAINT gestion_ai_commctem;
ALTER TABLE public.ctem
  ADD CONSTRAINT gestion_ai_commctem FOREIGN KEY (idcomm)
      REFERENCES public.comm (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

--======================================
-- nuevo trigger BEFORE DELETE del comm para que borre las patas ANTES que el propio comm
--======================================

-- DROP FUNCTION public.tg_comm_delete_movs()

CREATE OR REPLACE FUNCTION public.tg_comm_delete_movs()
  RETURNS trigger AS
$BODY$
BEGIN
-- borrar los movs ANTES que el comm para poder tener el comm en los triggers de los movs
IF (TG_OP = 'DELETE') THEN 
  DELETE FROM artm WHERE idcomm = OLD.id;
  DELETE FROM conm WHERE idcomm = OLD.id;
  DELETE FROM ctbm WHERE idcomm = OLD.id;
  DELETE FROM ctem WHERE idcomm = OLD.id;
END IF;
RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE  COST 100;
ALTER FUNCTION public.tg_comm_delete_movs() OWNER TO abacouser;

CREATE TRIGGER comm_005_del_movs
  BEFORE DELETE
  ON public.comm
  FOR EACH ROW
  EXECUTE PROCEDURE public.tg_comm_delete_movs();


--======================================
SELECT aaVersion('433.02', '433.01');
--======================================
